﻿/*******************************************************************************
 * Copyright © 2017 FriendStar.Framework 版权所有
 * Author: FZH
 * Description: FriendStar快速开发平台
 * Website：http://www.FriendStar.cn
*********************************************************************************/
using System.Collections.Generic;
using FriendStar.Code;
using System.Data.SqlClient;
using FriendStar.Application.VBaseData;
using System.Text;
using System.Data;
using System;
using Aspose.CAD;
using Aspose.CAD.FileFormats.Cad;
using Aspose.CAD.ImageOptions;
using System.IO;
using System.Collections;

namespace FriendStar.Application.BOM
{
    /// <summary>
	/// 下料卡对应的数据应用功能类。
	/// </summary>
	public class BlankingcardApp
    {
        public object GetBlankingcards(Pagination pagination, string matNO, string fProductionNO, string fProductionName, string MaterialCodeAll, string MaterialSizes_SJBL, string useUnit, bool isArchive)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select MatListID,useUnit,MatNO,	EditStatus	,IsBZGY	,IsImg,	Sign_MP,'编号：'+ISNULL(FProductionNO,'') + '<br/>名称：' + ISNULL(FProductionName,'') FProductionNOName,FProductionNO,FProductionName,
                                '工艺：' + MaterialCode_YCL + '<br/>' + '实际：' + MaterialCode_SJBL MaterialCode_ALL,MaterialCode_YCL,MaterialCode_SJBL,'工艺：' + MaterialSizes_BL + '<br/>' + '实际：' + MaterialSizes_SJBL MaterialSizes_ALL,MaterialSizes_BL,MaterialSizes_SJBL
                                ,MaterialSizes_K1,MaterialSizes_J	,FQuantity_GBZL	,FYZJK,	FYKJJ	,FYZJK_GY	,FYKJJ_GY	,KZLJ	,PartsNetWeight,MaterialQuota_GY,MaterialQuota_TL,LYL_GY,LYL_TL
                                ,FiberDirection,	RoughsType,RoughsSize	,case when kfgc is null and kzgc is null then '' else isnull(KFGC, '') + ',' + isnull(KZGC, '') end KFZGC,case when CFGC is null and czgc is null then '' else isnull(CFGC, '') + ',' + isnull(CZGC, '') end CZFGC,'工艺：' + StandardNumber_YCL + '<br/>实际：' + MaterialSizes_SJBL StandardNumber_ALL,StandardNumber_YCL
                                ,'工艺：' + MaterialDemand_YCL +'<br/>实际：'+ MaterialDemand_SJBL MaterialDemand_ALL,MaterialDemand_YCL,MaterialDemand_SJBL,FLGG1,FLZL1,	FLGG2,	FLZL2	,FLGG3	,FLZL3
                                YLGG1	,YLSL1,	YLGG2	,YLSL2	,YLGG3,	YLSL3,	InputUser,InputDate	,InputTime	,FAuditingBy	,FAuditingDate,	FAuditingTime
                                from(
                                select  MatListID,InputUser,InputDate	,InputTime	,FAuditingBy	,FAuditingDate,	FAuditingTime,FYKJJ_GY, MaterialSizes_BL,MaterialCode_YCL,MaterialCode_SJBL,useUnit,row_number () over (order by {4}) num, MatNO,case EditStatus when '0' then '编辑' when '1' then '发布' else '冻结' end as EditStatus,case IsBZGY when '是' then '标准' when '否' then '代用' else '' end as IsBZGY,case IsImg when '1' then '工艺' when '0' then '优选' else '' end as IsImg,Sign_MP,FProductionNO ,FProductionName,ProductUnit,ProduWorkshop,FQuantity_GB,case RoughsType when 'L' then ''  when 'Y' then ''  when 'T' then RIGHT(MaterialSizes_SJBL,5)   when 'K' then RIGHT(MaterialSizes_SJBL,5) else '' end as MaterialSizes_K1,MaterialSizes_J,FQuantity_GBZL,FYZJK,FYKJJ,FYZJK_GY,KZLJ,PartsNetWeight,MaterialQuota_GY,MaterialQuota_TL,LYL_GY,LYL_TL,case FiberDirection when 'A' then '是' else '' end as FiberDirection,case RoughsType when 'K' then '块料' when 'T' then '条料' when 'L' then '落料'  when 'Y' then '异形' else '' end as RoughsType,RoughsSize,KFGC,KZGC,CFGC,CZGC,StandardNumber_YCL,MaterialSizes_SJBL,MaterialDemand_YCL,MaterialDemand_SJBL,FLGG1,FLZL1,FLGG2,FLZL2,FLGG3,FLZL3,YLFX1,YLGG1,YLSL1,YLLY1,YLFX2,YLGG2,YLSL2,YLLY2,YLFX3,YLGG3,YLSL3,YLLY3
                                from {3}
                                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_XLK_22_BJ_MatCardListAcco";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (isArchive)
            {
                tableName = "V_XLK_21_MX_MatCardListAcco";
            }

            if (!string.IsNullOrEmpty(matNO))
            {
                conn.Append(string.Format(" and matNO = '{0}'", matNO));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(fProductionName))
            {
                conn.Append(string.Format(" and fProductionName = '{0}'", fProductionName));
            }

            if (!string.IsNullOrEmpty(MaterialCodeAll))
            {
                conn.Append(string.Format(" and MaterialCode_SJBL like '%{0}%'", MaterialCodeAll));
            }

            if (!string.IsNullOrEmpty(MaterialSizes_SJBL))
            {
                conn.Append(string.Format(" and RTRIM(LTRIM(MaterialSizes_SJBL)) like '%{0}%'", MaterialSizes_SJBL));
            }
            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetBlankingcardLayoutSets(Pagination pagination, string fProductionNO,string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"select   
                        MatListID,
                        MatNO,
	                        ProductUnit + '<br/>' + ProductUnitName ProductUnit_Name,
	                        ProduWorkshop + '<br/>' + ProduWorkshopName ProduWorkshop_Name,
	                        FQuantity_GB,
	                        Sign_MP,
	                        FProductionNO + '<br/>' + FProductionName FProductionNOName,
	                        FProductionNO,
	                        FProductionName,
	                        FQuantity_GBZL,
	                        MaterialCode_YCL,
	                        MaterialSizes_BL,
	                        MaterialCode_SJBL,
	                        MaterialSizes_SJBL,
	                        CASE RoughsType
                        WHEN 'L' THEN
	                        ''
                        WHEN 'Y' THEN
	                        ''
                        WHEN 'T' THEN
	                        RIGHT (MaterialSizes_SJBL, 5)
                        WHEN 'K' THEN
	                        RIGHT (MaterialSizes_SJBL, 5)
                        ELSE
	                        ''
                        END AS MaterialSizes_K1,
                         MaterialSizes_J,
                         KZLJ,
                         LYL_TL,
                         CASE FiberDirection
                        WHEN 'A' THEN
	                        '是'
                        ELSE
	                        ''
                        END AS FiberDirection,
                         CASE RoughsType
                        WHEN 'K' THEN
	                        '块料'
                        WHEN 'T' THEN
	                        '条料'
                        WHEN 'L' THEN
	                        '落料'
                        WHEN 'Y' THEN
	                        '异形'
                        ELSE
	                        ''
                        END AS RoughsType,
                         RoughsSize,
                         PartsNetWeight,
                         MaterialQuota_TL,
                         FLGG1,
                         FLZL1,
                         FLGG2,
                         FLZL2,
                         FLGG3,
                         FLZL3,
                         YLGG1,
                         YLSL1,
                         YLGG2,
                         YLSL2,
                         YLGG3,
                         YLSL3,
                         InputUser,
                         InputDate,
                         InputTime,
                         FAuditingBy,
                         FAuditingDate,
                         FAuditingTime,
                         BFFX
                                from(
                                select  row_number () over (order by {4}) num, *
                                from {3}
                                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_XLK_32_FA_MatCardListAcco";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object ConfirmLayoutSets(string matListID)
        {
            int result = 0;

            string sql = @"delete from XLK_22_BJ_MatCardListAcco
where MatListID = @MatListID;
INSERT INTO XLK_22_BJ_MatCardListAcco (
		MatListID,
		UseUnit,
		FProductionDate,
		FHappenComNO,
		FHappenComName,
		ProductUnit,
		ProduWorkshop,
		FProductionNO,
		FProductionName,
		MatNO,
		FQuantity_GB,
		MaterialNO,
		MaterialCode_YCL,
		MaterialSizes_BL,
		StandardNumber_YCL,
		MaterialDemand_YCL,
		MaterialCode_SJBL,
		MaterialSizes_SJBL,
		StandardNumber_SJBL,
		MaterialDemand_SJBL,
		Sign_MP,
		RoughsType,
		BFFX,
		FiberDirection,
		RoughsSize,
		KZGC,
		KFGC,
		CZGC,
		CFGC,
		MaterialSizes_J,
		FYZJK,
		FYKJJ,
		FYZJK_GY,
		FYKJJ_GY,
		KZLJ,
		FQuantity_GBZL,
		PartsNetWeight,
		MaterialQuota_GY,
		MaterialQuota_TL,
		LYL_GY,
		LYL_TL,
		FLGG1,
		FLZL1,
		FLGG2,
		FLZL2,
		FLGG3,
		FLZL3,
		YLFX1,
		YLGG1,
		YLSL1,
		YLLY1,
		YLFX2,
		YLGG2,
		YLSL2,
		YLLY2,
		YLFX3,
		YLGG3,
		YLSL3,
		YLLY3,
		YLFX4,
		YLGG4,
		YLSL4,
		YLLY4,
		YLFX5,
		YLGG5,
		YLSL5,
		YLLY5,
		YLFX6,
		YLGG6,
		YLSL6,
		YLLY6,
		FBZSM,
		IsBZGY,
		ISImg,
		IMG,
		IMG1,
		Pic_ID,
		MaintainStatus,
		EditStatus,
		InputUser,
		InputDate,
		InputTime,
		FAuditingBy,
		FAuditingDate,
		FAuditingTime
	)
select 
MatListID,
		UseUnit,
		FProductionDate,
		FHappenComNO,
		FHappenComName,
		ProductUnit,
		ProduWorkshop,
		FProductionNO,
		FProductionName,
		MatNO,
		FQuantity_GB,
		MaterialNO,
		MaterialCode_YCL,
		MaterialSizes_BL,
		StandardNumber_YCL,
		MaterialDemand_YCL,
		MaterialCode_SJBL,
		MaterialSizes_SJBL,
		StandardNumber_SJBL,
		MaterialDemand_SJBL,
		Sign_MP,
		RoughsType,
		BFFX,
		FiberDirection,
		RoughsSize,
		KZGC,
		KFGC,
		CZGC,
		CFGC,
		MaterialSizes_J,
		FYZJK,
		FYKJJ,
		FYZJK_GY,
		FYKJJ_GY,
		KZLJ,
		FQuantity_GBZL,
		PartsNetWeight,
		MaterialQuota_GY,
		MaterialQuota_TL,
		LYL_GY,
		LYL_TL,
		FLGG1,
		FLZL1,
		FLGG2,
		FLZL2,
		FLGG3,
		FLZL3,
		YLFX1,
		YLGG1,
		YLSL1,
		YLLY1,
		YLFX2,
		YLGG2,
		YLSL2,
		YLLY2,
		YLFX3,
		YLGG3,
		YLSL3,
		YLLY3,
		YLFX4,
		YLGG4,
		YLSL4,
		YLLY4,
		YLFX5,
		YLGG5,
		YLSL5,
		YLLY5,
		YLFX6,
		YLGG6,
		YLSL6,
		YLLY6,
		FBZSM,
		IsBZGY,
		ISImg,
		IMG,
		IMG1,
		Pic_ID,
		MaintainStatus,
		EditStatus,
		InputUser,
		InputDate,
		InputTime,
		FAuditingBy,
		FAuditingDate,
		FAuditingTime
FROM
	XLK_32_FA_MatCardListAcco
WHERE
	MatListID = @MatListID";
          
            SqlParameter par = new SqlParameter("@MatListID", matListID);

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, par);

            return result;
        }

        /// <summary>
        /// 排样
        /// </summary>
        /// <param name="selected">从下料卡添加表选择行数据</param>
        /// <param name="proNoNum">一张切几个</param>
        /// <param name="roughsSize">毛坯尺寸</param>
        /// <param name="fProductionNO">零件编号</param>
        /// <param name="materialCode_S">材料牌号</param>
        /// <param name="materialSizes_SH">材料规格厚度</param>
        /// <param name="materialSizes_SK">材料规格宽度</param>
        /// <param name="materialSizes_SC">材料规格长度</param>
        /// <param name="materialSizes_JK">剪切规格宽度</param>
        /// <param name="materialSizes_JC">剪切规格长度</param>
        /// <param name="useUnit">单位</param>
        /// <param name="curUser">当前用户</param>
        /// <returns>执行状态</returns>
        public object LayoutSet(Dictionary<string, object> selected, string proNoNum, string roughsSize, string fProductionNO, string materialCode_S, string materialSizes_SH, string materialSizes_SK, string materialSizes_SC, string materialSizes_JK, string materialSizes_JC, string useUnit, string curUser, string factoryFlag)
        {
            int result = 0;

            DateTime now = DateTime.Now;

            string tableName = "XLK_32_FA_MatCardListAcco", sMaterialSizes_S = string.Empty, sMaterialSizes_J = string.Empty;

            sMaterialSizes_S = BuildMaterialSize(materialSizes_SH, materialSizes_SK, materialSizes_SC);

            sMaterialSizes_J = BuildMaterialsCut(materialSizes_JK, materialSizes_JC);

            string cutType = GetCutType(selected["RoughsType"].ToString(), selected["FiberDirection"].ToString(), materialSizes_SC, roughsSize);
            //下料卡号
            string XLKNum = GetMatCardNum(useUnit, factoryFlag, "下料卡号", selected["RoughsType"].ToString(), cutType, curUser, "正式");

            string ssLBJH = fProductionNO;

            string ssLJMC = selected["FProductionName"].ToString();

            string ssCLBM = selected["MaterialNo"].ToString();
            //实际材料规格 厚*宽*长
            string ssJQGG = sMaterialSizes_J;
            //材料牌号
            string ssCLPH = materialCode_S;
            //剪切规格宽*长
            string ssCLGG = sMaterialSizes_S;
            //零件净重 PartsNetWeight
            string ssPartsNetWeight = selected["LsNetWeight"].ToString();
            //标准编号
            string ssBZBH = selected["StandardNumber"].ToString();
            //特殊要求
            string ssTSYQ = selected["MaterialDemand"].ToString();
            //材料牌号
            string ssGYCLPH = selected["MaterialCode"].ToString();
            //材料规格
            string ssGYCLGG = selected["MaterialSizes"].ToString();
            //标准编号
            string ssGYBZBH = selected["StandardNumber"].ToString();
            //特殊要求
            string ssGYTSYQ = selected["MaterialDemand"].ToString();

            string ssFisBZ = "是";

            if (ssCLPH.Trim() == ssGYCLPH.Trim() && ssCLGG.Trim() == ssGYCLGG.Trim())
            {
                ssFisBZ = "是";
            }
            else
            {
                ssFisBZ = "否";
            }

            string YLGG1 = "", YLGG2 = "";
            //方向
            string ssXWFX = selected["FiberDirection"].ToString();
            //毛坯类型
            string ssMPLB = selected["RoughsType"].ToString();
            //毛坯尺寸 CellWposiTolerance 
            string ssMPCC = roughsSize;
            //宽度正公差
            string ssKZGC = selected["WposiTolerance"].ToString();
            //宽度负公差
            string ssKFGC = selected["WnegaTolerance"].ToString();
            //长度正公差
            string ssCZGC = selected["HposiTolerance"].ToString();
            //长度负公差
            string ssCFGC = selected["HnegaTolerance"].ToString();
            //一张切几个零件
            string ssLJJS = proNoNum;

            int ZJZS = 1;
            if (ZJZS == 0)
            {
                ZJZS = 1;
            }
            int ssJQGGJS = 1;

            string MpK = "1";
            string MpC = "1";
            string MpDB = "0";
            if (ssMPLB == "K" || ssMPLB == "T")
            {
                int MPjs = ssMPCC.IndexOf("/");
                if (MPjs != -1)
                {
                    string[] MpStr = ssMPCC.Substring(0, MPjs).Split('x');
                    MpK = MpStr[0].ToString();
                    MpC = MpStr[1].ToString();
                }
                else
                {
                    string[] MpStr = ssMPCC.Split('x');
                    MpK = MpStr[0].ToString();
                    MpC = MpStr[1].ToString();
                }

                if (MPjs != -1)
                {
                    int y = ssMPCC.IndexOf("+");
                    if (y == -1)
                    {
                        MPjs = (int)decimal.Parse(ssMPCC.Substring(MPjs + 1).ToString());
                    }
                    else
                    {
                        MPjs = (int)decimal.Parse(ssMPCC.Substring(MPjs + 1, y - (MPjs + 1)).ToString());
                        MpDB = ssMPCC.Substring(y + 1).ToString();
                    }
                }
                else
                {
                    MPjs = 1;
                }
                if (decimal.Parse(MpC) == 0)
                {
                    ssJQGGJS = 1;
                }
                else
                {
                    ssJQGGJS = (int)((decimal.Parse(ssJQGG.Substring(ssJQGG.Length - 6)) - decimal.Parse(MpDB)) / decimal.Parse(MpC) * (MPjs == 0 ? 1 : MPjs));
                    ssJQGGJS = Math.Abs(ssJQGGJS == 0 ? 1 : ssJQGGJS);

                }
            }
            if (ssPartsNetWeight == "")
            {
                ssPartsNetWeight = "0";
            }
            double ssGBZL = 0;

            if (ssMPLB == "K" || ssMPLB == "T")
            {
                ssGBZL = double.Parse(ssCLGG.Substring(0, 5)) * double.Parse(ssCLGG.Substring(6, 4)) * double.Parse(ssCLGG.Substring(12, 4)) * 7.85 / 1000 / 1000;
            }
            else
            {
                ssGBZL = double.Parse(ssLJJS) * double.Parse(ssPartsNetWeight);

            }

            ArrayList SqlList = new ArrayList();

            int InsertInt = 0;

            decimal ProduNumber = 1;//可制件数

            decimal ProduNumber_Z = 1;//制件总数

            decimal YZJK = 1;//一张板切几块数

            decimal YKJJ = 1;//一块切几个零件数

            int FA1_KDSL = 0, FA1_CDSL = 0, FA1_KDYL = 0, FA1_CDYL = 0, FA1_GBZS = 1;

            int FA2_KDSL = 0, FA2_CDSL = 0, FA2_KDYL = 0, FA2_CDYL = 0, FA2_GBZS = 1;

            string FA1_YLGG1, FA1_YLGG2, FA2_YLGG1, FA2_YLGG2;

            string Crdate = now.ToString("yyyy-MM-dd");

            string CrTime = now.ToString("HH:mm:ss");
            //" + ssLBJH + "
            string DeleFA = string.Format(@"delete from {2} 
            where FProductionNO='{0}' and UseUnit='{1}'", ssLBJH, useUnit, tableName);

            SqlList.Add(DeleFA);

            if (ssCLGG.Length > 15 && (ssMPLB == "K" || ssMPLB == "T"))
            {
                //按毛坯宽度与板料宽度一致的方向进行排样 --- 方案一
                FA1_KDSL = (int)decimal.Parse(ssCLGG.Substring(6, 4)) / (int)decimal.Parse(ssJQGG.Substring(0, 6));

                FA1_CDSL = (int)decimal.Parse(ssCLGG.Substring(12, 4)) / (int)decimal.Parse(ssJQGG.Substring(ssJQGG.Length - 6));
                #region
                if ((FA1_KDSL > 0) && (FA1_CDSL > 0))
                {
                    FA1_KDYL = (int)decimal.Parse(ssCLGG.Substring(6, 4)) - FA1_KDSL * (int)decimal.Parse(ssJQGG.Substring(0, 6));

                    FA1_CDYL = (int)decimal.Parse(ssCLGG.Substring(12, 4)) - FA1_CDSL * (int)decimal.Parse(ssJQGG.Substring(ssJQGG.Length - 6));

                    string KDStr = "    " + string.Format("{0:N0}", FA1_KDYL);

                    string CDStr = "    " + string.Format("{0:N0}", FA1_CDYL);

                    FA1_YLGG1 = FA1_KDYL == 0 ? "" : (KDStr.Substring(KDStr.Length - 4) + "x" + ssJQGG.Substring(ssJQGG.Length - 6));

                    if (FA1_YLGG1 == "")
                    {
                        FA1_YLGG2 = FA1_CDYL == 0 ? "" : (ssCLGG.Substring(6, 4) + "x" + CDStr.Substring(CDStr.Length - 5));
                    }
                    else
                    {
                        FA1_YLGG2 = FA1_CDYL == 0 ? "" : (ssCLGG.Substring(6, 4) + "x" + CDStr.Substring(CDStr.Length - 5));

                    }

                    YLGG1 = FA1_YLGG1;

                    YLGG2 = FA1_YLGG2;

                    double FA1_GBZL = Math.Round(FA1_GBZS * ssGBZL, 4, MidpointRounding.AwayFromZero);// double.Parse(string.Format("0.0000", FA1_GBZS * ssGBZL));//钢板总量

                    ProduNumber = FA1_KDSL * FA1_CDSL * ssJQGGJS;//可制件数                                 

                    ProduNumber_Z = FA1_GBZS * FA1_KDSL * FA1_CDSL * ssJQGGJS;//制件总数

                    YZJK = FA1_KDSL * FA1_CDSL;

                    YKJJ = ssJQGGJS;

                    int YLSL1 = 0, YLSL2 = 0;

                    YLSL1 = FA1_YLGG1 == "" ? 0 : FA1_CDSL;

                    YLSL2 = FA1_YLGG2 == "" ? 0 : FA1_GBZS;

                    if (FA1_YLGG1.Equals(string.Empty))
                    {
                        FA1_YLGG1 = FA1_YLGG2;

                        YLSL1 = YLSL2;

                        FA1_YLGG2 = "";

                        YLSL2 = 0;
                    }
                    else
                    {
                        string YL2 = "     " + ((int)decimal.Parse(ssCLGG.Substring(12, 4)) - FA1_CDYL).ToString();

                        FA1_YLGG1 = FA1_CDYL == 0 ? (KDStr.Substring(KDStr.Length - 4) + "x " + ssCLGG.Substring(12, 4)) : (KDStr.Substring(KDStr.Length - 4) + "x" + YL2.Substring(YL2.Length - 5));

                        YLSL1 = 1;
                    }
                    string StrInsertFA = string.Format(@"insert into {0}(
                                                         MatListID,
                                                         UseUnit,
                                                         FProductionDate,
                                                         ProductUnit,
                                                         ProduWorkshop,
                                                         MatNO,
                                                         FQuantity_GB,
                                                         Sign_MP,
                                                         FProductionNO,
                                                         FProductionName,
                                                         FHappenComNO,
                                                         FHappenComName,
                                                         MaterialNO,
                                                         MaterialCode_YCL,
                                                         MaterialSizes_BL,
                                                        StandardNumber_YCL,
                                                         MaterialDemand_YCL,
                                                         MaterialCode_SJBL,
                                                         MaterialSizes_SJBL,
                                                         StandardNumber_SJBL,
                                                         MaterialDemand_SJBL,
                                                         FQuantity_GBZL,
                                                         PartsNetWeight,
                                                         RoughsType,
                                                         FiberDirection,
                                                         RoughsSize,
                                                         KZGC,
                                                         KFGC,
                                                         CZGC,
                                                         CFGC,
                                                         MaterialSizes_J,
                                                         KZLJ,
                                                         YLFX1,
                                                         YLGG1,
                                                         YLSL1,
                                                         YLLY1,
                                                         YLFX2,
                                                         YLGG2,
                                                         YLSL2,
                                                         YLLY2,
                                                         YLFX3,
                                                         YLGG3,
                                                         YLSL3,
                                                         YLLY3,
                                                         MaintainStatus,
                                                         EditStatus,
                                                         InputUser,
                                                         InputDate,
                                                         InputTime,
                                                         FYZJK_GY,
                                                         FYKJJ_GY,
                                                         IsImg,
                                                        LYL_TL,
                                                        MaterialQuota_TL
                                                        ) ",tableName);

                    StrInsertFA = string.Format(@"{54} values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}'
                                                                    ,'{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}'
                                                                    ,'{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}'
                                                                    ,'{41}','{42}','{43}','{44}','{45}','{46}','{47}','{48}','{49}','{50}','{51}','{52}','{53}')",
                                                                    Guid.NewGuid(),
                                                                    useUnit,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    string.Format("{0}_1", XLKNum),
                                                                    FA1_GBZS,
                                                                    "W1",
                                                                    fProductionNO,
                                                                    ssLJMC,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    ssCLBM,
                                                                    ssGYCLPH,
                                                                    ssGYCLGG,
                                                                    ssGYBZBH,
                                                                    ssGYTSYQ,
                                                                    ssCLPH,
                                                                    ssCLGG,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    FA1_GBZL,
                                                                    ssPartsNetWeight,
                                                                    selected["RoughsType"].ToString(),
                                                                    string.Empty,
                                                                    roughsSize,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    sMaterialSizes_J,
                                                                    ProduNumber,
                                                                    string.Empty,
                                                                    FA1_YLGG1,
                                                                    YLSL1,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    FA1_YLGG2,
                                                                    YLSL2,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    '0',
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    string.Empty,
                                                                    curUser,
                                                                    Crdate,
                                                                    CrTime,
                                                                    YZJK,
                                                                    YKJJ,
                                                                    '0',
                                                                    '0',
                                                                    '0',
                                                                    StrInsertFA);

                    SqlList.Add(StrInsertFA);

                    decimal LYBL = ProduNumber / ssJQGGJS * decimal.Parse(MpK) * decimal.Parse(MpC) / decimal.Parse(ssCLGG.Substring(6, 4)) / decimal.Parse(ssCLGG.Substring(12, 4)) * 100;

                    string UpdateFA = string.Format(" UPDATE {0} SET LYL_TL=PartsNetWeight*KZLJ/(FQuantity_GBZL)*100,MaterialQuota_TL=FQuantity_GBZL/KZLJ where FQuantity_GBZL<>0 and MatNO='{0}_1" + "'", tableName, XLKNum);

                    SqlList.Add(UpdateFA);

                    InsertInt += 1;
                }
                if (ssXWFX == "")
                {
                    //按毛坯长度与板料宽度一致的方向进行排样 --- 方案二
                    FA2_KDSL = (int)decimal.Parse(ssCLGG.Substring(6, 4)) / (int)decimal.Parse(ssJQGG.Substring(ssJQGG.Length - 6));

                    FA2_CDSL = (int)decimal.Parse(ssCLGG.Substring(12, 4)) / (int)decimal.Parse(ssJQGG.Substring(0, 6));

                    if ((FA2_KDSL > 0) && (FA2_CDSL > 0))
                    {
                        FA2_KDYL = (int)decimal.Parse(ssCLGG.Substring(6, 4)) - FA2_KDSL * (int)decimal.Parse(ssJQGG.Substring(ssJQGG.Length - 6));

                        FA2_CDYL = (int)decimal.Parse(ssCLGG.Substring(12, 4)) - FA2_CDSL * (int)decimal.Parse(ssJQGG.Substring(0, 6));

                        string KDStr2 = "    " + string.Format("{0:N0}", FA2_KDYL);

                        string CDStr2 = "    " + string.Format("{0:N0}", FA2_CDYL);

                        FA2_YLGG1 = FA2_KDYL == 0 ? "" : (KDStr2.Substring(KDStr2.Length - 4) + "x" + ssJQGG.Substring(0, 6));

                        if (FA2_YLGG1 == "")
                        {
                            FA2_YLGG2 = FA2_CDYL == 0 ? "" : (ssCLGG.Substring(6, 4) + "x" + CDStr2.Substring(CDStr2.Length - 5));
                        }
                        else
                        {
                            FA2_YLGG2 = FA2_CDYL == 0 ? "" : (ssCLGG.Substring(6, 4) + "x" + CDStr2.Substring(CDStr2.Length - 5));
                        }

                        double FA2_GBZL = Math.Round(FA2_GBZS * ssGBZL, 4, MidpointRounding.AwayFromZero); //double.Parse(string.Format("0.0000", FA2_GBZS * ssGBZL));//钢板总量

                        ProduNumber = FA2_KDSL * FA2_CDSL * ssJQGGJS;//可制件数

                        ProduNumber_Z = FA2_GBZS * FA2_KDSL * FA2_CDSL * ssJQGGJS;//制件总数

                        YZJK = FA2_KDSL * FA2_CDSL;

                        YKJJ = ssJQGGJS;

                        int YLSL1 = 0, YLSL2 = 0;

                        YLSL1 = FA2_YLGG1 == "" ? 0 : FA2_CDSL;

                        YLSL2 = FA2_YLGG2 == "" ? 0 : FA2_GBZS;

                        if (FA2_YLGG1 == "")
                        {
                            FA2_YLGG1 = FA2_YLGG2;

                            YLSL1 = YLSL2;

                            FA2_YLGG2 = "";

                            YLSL2 = 0;
                        }
                        else
                        {
                            string YL2 = "     " + ((int)decimal.Parse(ssCLGG.Substring(12, 4)) - FA2_CDYL).ToString();

                            FA2_YLGG1 = FA2_CDYL == 0 ? (KDStr2.Substring(KDStr2.Length - 4) + "x " + ssCLGG.Substring(12, 4)) : (KDStr2.Substring(KDStr2.Length - 4) + "x" + YL2.Substring(YL2.Length - 5));

                            YLSL1 = 1;
                        }

                        string StrInsertFA2 = string.Format(@"insert into {54}(
                                                            MatListID,
                                                             UseUnit,
                                                             FProductionDate,
                                                             ProductUnit,
                                                             ProduWorkshop,
                                                             MatNO,
                                                             FQuantity_GB,
                                                             Sign_MP,
                                                             FProductionNO,
                                                             FProductionName,
                                                             FHappenComNO,
                                                             FHappenComName,
                                                             MaterialNO,
                                                             MaterialCode_YCL,
                                                             MaterialSizes_BL,
                                                             StandardNumber_YCL,
                                                            MaterialDemand_YCL,
                                                            MaterialCode_SJBL,
                                                         MaterialSizes_SJBL,
                                                         StandardNumber_SJBL,
                                                         MaterialDemand_SJBL,
                                                             FQuantity_GBZL,
                                                             PartsNetWeight,
                                                             RoughsType,
                                                             FiberDirection,
                                                             RoughsSize,
                                                             KZGC,
                                                             KFGC,
                                                             CZGC,
                                                             CFGC,
                                                             MaterialSizes_J,
                                                             KZLJ,
                                                           YLFX1,
                                                             YLGG1,
                                                             YLSL1,
                                                             YLLY1,
                                                             YLFX2,
                                                             YLGG2,
                                                             YLSL2,
                                                             YLLY2,
                                                             YLFX3,
                                                             YLGG3,
                                                             YLSL3,
                                                             YLLY3,
                                                              MaintainStatus,
                                                            EditStatus,
                                                             InputUser,
                                                             InputDate,
                                                             InputTime,
                                                             FYZJK_GY,
                                                             FYKJJ_GY,
                                                             IsImg,
                                                            LYL_TL,
                                                            MaterialQuota_TL
                                                            ) values( '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}'
                                                                                    ,'{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}',
                                                                                   '{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}',
                                                                                    '{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}',
                                                                                    '{41}','{42}','{43}','{44}','{45}','{46}','{47}','{48}','{49}','{50}',
                                                                                    '{51}','{52}','{53}')",










                                                                                    Guid.NewGuid(),
                                                                                  useUnit,
                                                                                  string.Empty,
                                                                                  string.Empty,
                                                                                  string.Empty,
                                                                                  string.Format("'{0}_2'", XLKNum),
                                                                                  FA2_GBZS,
                                                                                  "W1",
                                                                                  fProductionNO,
                                                                                  ssLJMC,
                                                                                  string.Empty,
                                                                                  string.Empty,
                                                                                  ssCLBM,
                                                                                  ssGYCLPH,
                                                                                  ssGYCLGG,
                                                                                  ssGYBZBH,
                                                                                  ssGYTSYQ,
                                                                                  ssCLPH,
                                                                                  ssCLGG,
                                                                                  string.Empty,
                                                                                  string.Empty,
                                                                                  FA2_GBZL,
                                                                                  ssPartsNetWeight,
                                                                                  selected["RoughsType"],
                                                                                   string.Empty,
                                                                                   roughsSize,
                                                                                   string.Empty,
                                                                                   string.Empty,
                                                                                   string.Empty,
                                                                                   string.Empty,
                                                                                  sMaterialSizes_J,
                                                                                 ProduNumber,
                                                                                  string.Empty,
                                                                                  FA2_YLGG1,
                                                                                  YLSL1,
                                                                                  string.Empty,
                                                                                  string.Empty,
                                                                                 FA2_YLGG2,
                                                                                 YLSL2,
                                                                                 string.Empty,
                                                                                 string.Empty,
                                                                                 string.Empty,
                                                                                 '0',
                                                                                 string.Empty,
                                                                                 string.Empty,
                                                                                 string.Empty,
                                                                                 curUser,
                                                                                Crdate,
                                                                                CrTime,
                                                                                YZJK,
                                                                                YKJJ,
                                                                                '0',
                                                                                '0',
                                                                                '0',tableName
                                                                                    );



                        StrInsertFA2 = StrInsertFA2 + @" values( newid(),'" + useUnit + "','','','','" + XLKNum + "_2" + "','" + FA2_GBZS + "','W1','" + fProductionNO + "','" + ssLJMC + "','','','" + ssCLBM + "','" + ssGYCLPH + "','" + ssGYCLGG + "','" + ssGYBZBH + "','" + ssGYTSYQ + "','" + ssCLPH + "','" + ssCLGG + "','','0' ,'','','" + FA2_GBZL + "'  ,'" + ssPartsNetWeight + "','" + selected["RoughsType"].ToString() + "','','" + roughsSize + "','','','','','" + sMaterialSizes_J + "','C','" + ProduNumber + "','" + ProduNumber_Z + "','0','','" + FA2_YLGG1 + "','" + YLSL1 + "','','','" + FA2_YLGG2 + "','" + YLSL2 + "','','','','0','' ,'','','" + curUser + "','" + Crdate + "','" + CrTime + "','" + ssPartsNetWeight + "','" + YZJK + "','" + YKJJ + "','" + ssFisBZ + "','0'  )";

                        SqlList.Add(StrInsertFA2);

                        decimal LYBL = ProduNumber / ssJQGGJS * decimal.Parse(MpK) * decimal.Parse(MpC) / decimal.Parse(ssCLGG.Substring(6, 4)) / decimal.Parse(ssCLGG.Substring(12, 4)) * 100;

                        string UpdateFA = string.Format(" UPDATE {0} SET LYL_TL=PartsNetWeight*KZLJ/(FQuantity_GBZL/FQuantity_GB)*100,MaterialQuota_TL=FQuantity_GBZL/KZLJ where FQuantity_GBZL<>0 and FQuantity_GB<>0 and MatNO='{1}_2" + "'", tableName, XLKNum);

                        SqlList.Add(UpdateFA);

                        InsertInt += 1;
                    }
                }
                #endregion
            }

            result = SqlHelper.ExecTran(SqlList) ? 1 : 0;

            return result;
        }

        private string GetMatCardNum(string useUnit, string factoryFlag, string FItemDesc, string RoughsType, string cutType, string curUser, string DocNumType)
        {
            string result = string.Empty;

            SqlParameter par = new SqlParameter("@DocumentNum", result);

            par.Direction = ParameterDirection.Output;
            //exec GetMatCardNum '25000','CY','下料卡号','L','2','001',@DocumentNum out,'正式'
            string sql = string.Format("exec GetMatCardNum '{0}','{1}','{2}','{3}','{4}','{5}',@DocumentNum out,'{6}' select @DocumentNum"
                , useUnit,factoryFlag,FItemDesc,RoughsType,cutType,curUser,DocNumType);

            result = SqlHelper.ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, sql, par).ToString();

            return result;
        }

        private string GetCutType(string RoughsType, string FiberDirection, string materialSizeW, string roughsSize)
        {
            string result = string.Empty;

            List<string> typies = new List<string>("C,T,K,L,Y".Split(','));

            decimal roughsSizeW = 0;

            decimal roughsSizeL = 0;

            string[] t = roughsSize.Split('/')[0].Split('x');

            roughsSizeW = Convert.ToDecimal(t[0].Trim());

            roughsSizeL = Convert.ToDecimal(t[1].Trim());

            if (3 <= typies.IndexOf(RoughsType))
            {
                result = "0";
            }

            if (3 > typies.IndexOf(RoughsType) && 0 <= typies.IndexOf(RoughsType))
            {
                if (2 == typies.IndexOf(RoughsType))
                {
                    result = "1";
                }
                else
                {
                    if (0==typies.IndexOf(RoughsType))
                    {
                        result = "2";
                    }else
                    {
                        if (RoughsType.Equals("0"))
                        {
                            result = "0";
                        }
                    }
                }
            }

            return result;
        }

        public string BuildMaterialsCut(string materialSizes_JK, string materialSizes_JC)
        {
            StringBuilder result = new StringBuilder();

            if (!materialSizes_JK.Trim().Equals(string.Empty))
            {
                result.Append(materialSizes_JK.PadLeft(6, ' '));
            }

            if (!materialSizes_JC.Trim().Equals(string.Empty))
            {
                result.Append(string.Format("x{0}", materialSizes_JC.PadLeft(6, ' ')));
            }

            return result.ToString();
        }

        public string BuildMaterialSize(string materialSizes_SH, string materialSizes_SK, string materialSizes_SC)
        {
            StringBuilder result = new StringBuilder();

            if (!materialSizes_SH.Trim().Equals(string.Empty))
            {
                result.Append(materialSizes_SH.Trim().PadLeft(5, ' '));
            }

            if (!materialSizes_SK.Trim().Equals(string.Empty))
            {
                result.Append(string.Format("x{0}", materialSizes_SK.Trim().PadLeft(4, ' ')));
            }

            if (!materialSizes_SC.Trim().Equals(string.Empty))
            {
                result.Append(string.Format("x{0}", materialSizes_SC.Trim().PadLeft(5, ' ')));
            }

            return result.ToString();
        }

        public object FrozenBlankcardItem(string matNO, string useUnit)
        {
            int result = 0;

            string sql = @"UPDATE XLK_21_MX_MatCardListAcco  SET  EditStatus ='2' where UseUnit =@UseUnit  and MatNO =@MatNO";

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", useUnit),
                new SqlParameter("@MatNO", matNO)
            };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public byte[] GetArtMatCardMaterialsPicView(string pic_NO)
        {
            byte[] result = null;

            SqlParameter[] pars = null;

            string sql = string.Empty;

            try
            {
                sql = @"select pic_no from XLK_25_PC_MatCardImage
                where  pic_id = @pic_NO";

                pars = new SqlParameter[] {
                    new SqlParameter("@pic_NO", pic_NO)
                };

                string base64Str = (string)SqlHelper.ExecuteScalar(SqlHelper.GetConnSting(), CommandType.Text, sql, pars);

                result = Convert.FromBase64String(base64Str);
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }

            return result;
        }

        public object GetBlankcardPrintInfo(string matNO, string useUnit, bool isArchive)
        {
            Dictionary<string, object> result = null;

            string sql = @"select 
--文件代号：
MatNO,
--供货单位 : 
ISNULL(ProduWorkshop,'') ProduWorkshop,
--符号：
ISNULL(Sign_MP,'') Sign_MP,
--零件编号：
ISNULL(FProductionNO,'') FProductionNO,
ISNULL(FProductionName,'') FProductionName,
--工艺要求
ISNULL(MaterialCode_YCL,'') MaterialCode_YCL,
ISNULL(MaterialSizes_BL,'') MaterialSizes_BL,
--实际投料：
ISNULL(MaterialCode_SJBL,'') MaterialCode_SJBL,
ISNULL(MaterialSizes_SJBL,'') MaterialSizes_SJBL,
--毛坯尺寸(KFGC,KZGC)  (KFGC, CZGC),
case when KFGC is null and KZGC is null then '' else '('+ KFGC + ',' + KZGC +')' end KFGC_KZGC,
case when KFGC is null and CZGC is null then '' else '('+ KFGC + ',' + CZGC +')' end KFGC_CZGC,
ISNULL(RoughsType,'') RoughsType,
ISNULL(RoughsSize,'') RoughsSize,
--其中RoughsType ,  取K,T,Y,L
--每张钢板可制件数 
ISNULL(KZLJ,0) KZLJ,
--制件总数    
--余料规格  重量           块数
ISNULL(YLGG1,'') YLGG1,
--		计算得出		YLSL1,
ISNULL(YLGG2,'') YLGG2,
--		计算得出	    YLSL2,
ISNULL(YLGG3,'') YLGG3,
--	计算得出		YLSL3
ISNULL(dbo.calc_w_h(YLGG1,RoughsSize,YLSL1),'') YLZL1,
ISNULL(dbo.calc_w_h(YLGG2,RoughsSize,YLSL2),'') YLZL2,
ISNULL(dbo.calc_w_h(YLGG3,RoughsSize,YLSL3),'') YLZL3,
case  when YLSL1 is null then '' else  YLSL1 end YLSL1,
ISNULL(YLSL2,0) YLSL2,
ISNULL(YLSL3,0) YLSL3,
ISNULL(FLGG1,'') FLGG1,
ISNULL(FLGG2,'') FLGG2,
ISNULL(FLGG3,'') FLGG3,
ISNULL(FLZL1,0) FLZL1,
ISNULL(FLZL2,0) FLZL2,
ISNULL(FLZL3,0) FLZL3,
--备注：
ISNULL(FBZSM,'') FBZSM,
--编辑日期：
ISNULL(InputUser,'') InputUser,
ISNULL(InputDate,'') InputDate,
   ISNULL(InputTime,'') InputTime,
--批准日期：
ISNULL(FAuditingBy,'') FAuditingBy,
ISNULL(FAuditingDate,'') FAuditingDate,
ISNULL(FAuditingTime,'') FAuditingTime,
ISNULL(IsImg,'') IsImg,
pic_id
                                from {0} where useUnit = @useUnit and MatNO = @MatNO", tableName = "V_XLK_22_BJ_MatCardListAcco";

            if (!isArchive)
            {
                tableName = "V_XLK_21_MX_MatCardListAcco";
            }

            sql = string.Format(sql, tableName);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit),
                new SqlParameter("@MatNO", matNO)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                if (reader.Read())
                {
                    result = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        result.Add(reader.GetName(i), reader[i]);
                    }
                }
            }

            return result;
        }

        public object GetBlankingcardMaterialBoards(Pagination pagination, string MaterialCode, string materialSizesT, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT 
FStoreNO	,FStoreSetNO	,MaterialCode	,MaterialSizes	,FBatch,	FQuantity_RK	,FQuantity_CK,	FQuantity_RK-FQuantity_CK FQuantity_RK_FQuantity_CK,
	FZS_RK-FZS_CK	FZS_RK_FZS_CK,FOvenNum,	WagonNumber	,Fprice,	
(FQuantity_RK-FQuantity_CK)*ISNULL(Fprice,0) FQuantity_RK_FQuantity_CK_Fprice,
	FHappenCompanyNO	,FHappenCompanyName	,FDocuNO	,FCreateDate	,FProCompanyName	,StandardNumber	,MaterialDemand	,FDescription
FROM
	(
		SELECT
			row_number () OVER (

				ORDER BY FCreateDate  DESC,FCreateTime  DESC
			) num,
			*
		FROM
			{3}
	) a WHERE num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_CLK_61_PC_MaterialProAIAOAcco";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(MaterialCode))
            {

                conn.Append(string.Format(" and MaterialCode like '%{0}%'", MaterialCode));
            }

            if (!string.IsNullOrEmpty(materialSizesT))
            {
                string t = string.Format("{0:#0.00}", Convert.ToDouble(materialSizesT.Trim()));

                conn.Append(string.Format(" and materialSizes like '{0}%'", t.PadLeft(5, ' ')));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName);

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetBlankingcardAdds(Pagination pagination, string productionNO, string materialCodeAll, string materialSizesAll, string isBZGY, string isImg, string roughsType, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
                        CldeID,
                        LsNetWeight,
                        MaterialNo,
                        FiberDirection,
	                        '编号：' + FProductionNO + '<br/>名称：' + FProductionName FProductionNOName,
	                        FProductionNO,
	                        FProductionName,
	                        CASE IsBZGY
                        WHEN '是' THEN
	                        '标准'
                        WHEN '否' THEN
	                        '代用'
                        ELSE
	                        ''
                        END AS IsBZGYName,
                         IsBZGY,
                         CASE ISImg
                        WHEN '1' THEN
	                        '工艺'
                        WHEN '0' THEN
	                        '优选'
                        ELSE
	                        ''
                        END AS ISImgName,
                         ISImg,
                         '工艺：' + ISNULL(MaterialCode, '') + '<br/>实际：' + ISNULL(MaterialCode_SJBL, '') MaterialCode_ALL,
                         MaterialCode,
                         MaterialCode_SJBL,
                         '工艺：' + ISNULL(MaterialSizes, '') + '<br/>实际：' + ISNULL(MaterialSizes_SJBL, '') MaterialSizes_ALL,
                         MaterialSizes,
                         MaterialSizes_SJBL,
                         CASE RoughsType
                        WHEN 'L' THEN
	                        ''
                        WHEN 'Y' THEN
	                        ''
                        WHEN 'T' THEN
	                        CASE
                        WHEN MaterialCode_SJBL IS NULL THEN
	                        ''
                        ELSE
	                        RIGHT (MaterialCode_SJBL, 5)
                        END
                        WHEN 'K' THEN
	                        CASE
                        WHEN MaterialCode_SJBL IS NULL THEN
	                        ''
                        ELSE
	                        RIGHT (MaterialCode_SJBL, 5)
                        END
                        ELSE
	                        ''
                        END AS MaterialSizes_K1,
                         MaterialSizes_J,
                         FYZJK,
                         FYKJJ,
                         FYZJK_GY,
                         FYKJJ_GY,
                         KZLJ,
                         CASE Fiberdirection
                        WHEN 'A' THEN
	                        '是'
                        ELSE
	                        ''
                        END AS FiberdirectionName,
                         Fiberdirection,
                         CASE RoughsType
                        WHEN 'K' THEN
	                        '块料'
                        WHEN 'T' THEN
	                        '条料'
                        WHEN 'L' THEN
	                        '落料'
                        WHEN 'Y' THEN
	                        '异形'
                        ELSE
	                        ''
                        END AS RoughsTypeName,
                         RoughsType,
                         RoughsSize,
                         CASE
                        WHEN WnegaTolerance IS NULL
                        AND WposiTolerance IS NULL THEN
	                        ''
                        ELSE
	                        '(' + WnegaTolerance + ',' + WposiTolerance + ')'
                        END WnegaToleranceWposiTolerance,
                        WnegaTolerance,
                        WposiTolerance,
                         CASE
                        WHEN HnegaTolerance IS NULL
                        AND HposiTolerance IS NULL THEN
	                        ''
                        ELSE
	                        '(' + HnegaTolerance + ',' + HposiTolerance + ')'
                        END HnegaToleranceHposiTolerance,
                        HnegaTolerance,
                        HposiTolerance,
                         '工艺：' + ISNULL(StandardNumber, '') + '<br/>实际：' + ISNULL(StandardNumber_SJBL, '') StandardNumber_ALL,
                         '工艺：' + ISNULL(MaterialDemand, '') + '<br/>实际：' + ISNULL(MaterialDemand_SJBL, '') MaterialDemand_ALL,
                        StandardNumber,
                        MaterialDemand,

                         CombineNO,
                         FBZSM,
                         InputUser,
                         InputDate,
                         InputTime
FROM
	(
		SELECT
			row_number () OVER (ORDER BY {4}) num,
			*
		FROM
			{3}
	) a
                WHERE num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_XLK_22_BJ_MatCardListAcco_Add";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(productionNO))
            {
                conn.Append(string.Format(" and productionNO like '%{0}%'", productionNO));
            }

            if (!string.IsNullOrEmpty(materialCodeAll))
            {
                conn.Append(string.Format(" and MaterialCode_SJBL like '%{0}%' or MaterialCode like '%{0}%'", materialCodeAll));
            }

            if (!string.IsNullOrEmpty(materialSizesAll))
            {
                conn.Append(string.Format(" and MaterialSizes  like '%{0}%' or MaterialSizes_SJBL like '%{0}%'", materialSizesAll));
            }

            if (!string.IsNullOrEmpty(isBZGY))
            {
                conn.Append(string.Format(" and isBZGY = '{0}'", isBZGY));
            }

            if (!string.IsNullOrEmpty(isImg))
            {
                conn.Append(string.Format(" and isImg = '{0}'", isImg));
            }
            if (!string.IsNullOrEmpty(roughsType))
            {
                conn.Append(string.Format(" and roughsType = '{0}'", roughsType));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public byte[] GetArtMatCardMaterialsPic(string pic_NO)
        {
            byte[] result = null;

            SqlParameter[] pars = null;

            string sql = string.Empty;

            try
            {
                sql = @"select Material_no from XLK_24_TK_MatCardMaterialsPicDB
                where  pic_NO = @pic_NO";

                pars = new SqlParameter[] {
                    new SqlParameter("@pic_NO", pic_NO)
                };

                string base64Str = (string)SqlHelper.ExecuteScalar(SqlHelper.GetConnSting(), CommandType.Text, sql, pars);

                result = Convert.FromBase64String(base64Str);
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }

            return result;
        }

        public System.Drawing.Image ConvertDwgToPng(byte[] dwgFile)
        {
            LicenseHelper.ModifyInMemory.ActivateMemoryPatching();

            System.Drawing.Image result = null;

            using (Image image = Image.Load(BytesToStream(dwgFile)))
            {
                CadImage cadImage = (CadImage)image;

                CadRasterizationOptions rasterizationOptions = new CadRasterizationOptions();

                Console.WriteLine("CAD长度：{0}，宽度：{1}。单位：{2}", cadImage.Width, cadImage.Height, image.UnitType);

                int maxWidth = 3000;

                int maxHeight = Convert.ToInt32(maxWidth / (cadImage.Width / (cadImage.Height * 1.0)));

                rasterizationOptions.PageWidth = (300 > cadImage.Width ? maxWidth : cadImage.Width);

                rasterizationOptions.PageHeight = (300 > cadImage.Height ? maxHeight : cadImage.Height);

                rasterizationOptions.CenterDrawing = true;

                rasterizationOptions.NoScaling = false;

                PngOptions pngOptions = new PngOptions();

                pngOptions.VectorRasterizationOptions = rasterizationOptions;

                Stream st = new MemoryStream();

                image.Save(st, pngOptions);

                result = System.Drawing.Image.FromStream(st);
            }

            return result;
        }

        private static byte[] StreamToBytes(Stream stream)
        {
            byte[] bytes = new byte[stream.Length];
            stream.Read(bytes, 0, bytes.Length);
            // 设置当前流的位置为流的开始
            stream.Seek(0, System.IO.SeekOrigin.Begin);
            return bytes;
        }

        /// 将 byte[] 转成 Stream
        private static Stream BytesToStream(byte[] bytes)
        {
            Stream stream = new MemoryStream(bytes);
            return stream;
        }

        public object GetBlankingcard(string MatListID,bool isArchive)
        {
            Dictionary<string, object> result = null;

            string sql = @"select MatListID,useUnit,MatNO,	EditStatus	,IsBZGY	,IsImg,	Sign_MP,'编号：'+ISNULL(FProductionNO,'') + '<br/>名称：' + ISNULL(FProductionName,'') FProductionNOName,FProductionNO,FProductionName,
                                '工艺：' + MaterialCode_YCL + '<br/>' + '实际：' + MaterialCode_SJBL MaterialCode_ALL,MaterialCode_YCL,MaterialCode_SJBL,'工艺：' + MaterialSizes_BL + '<br/>' + '实际：' + MaterialSizes_SJBL MaterialSizes_ALL,MaterialSizes_BL,MaterialSizes_SJBL
                                ,MaterialSizes_K1,MaterialSizes_J	,FQuantity_GBZL	,FYZJK,	FYKJJ	,FYZJK_GY	,FYKJJ_GY	,KZLJ	,PartsNetWeight,MaterialQuota_GY,MaterialQuota_TL,LYL_GY,LYL_TL
                                ,FiberDirection,	RoughsType	,RoughsSize	,isnull(KFGC,'')+','+isnull(KZGC,'') KFZGC,isnull(CFGC,'')+','+isnull(CZGC,'') CZFGC,'工艺：' + StandardNumber_YCL + '<br/>实际：' + MaterialSizes_SJBL StandardNumber_ALL,StandardNumber_YCL
                                ,'工艺：' + MaterialDemand_YCL +'<br/>实际：'+ MaterialDemand_SJBL MaterialDemand_ALL,MaterialDemand_YCL,MaterialDemand_SJBL,FLGG1,FLZL1,	FLGG2,	FLZL2	,FLGG3	,FLZL3
                                YLGG1	,YLSL1,	YLGG2	,YLSL2	,YLGG3,	YLSL3,	InputUser,InputDate	,InputTime	,FAuditingBy	,FAuditingDate,	FAuditingTime
                                from(
                                select  MatListID,InputUser,InputDate	,InputTime	,FAuditingBy	,FAuditingDate,	FAuditingTime,FYKJJ_GY, MaterialSizes_BL,MaterialCode_YCL,MaterialCode_SJBL,useUnit,row_number () over (order by matNO) num, MatNO,case EditStatus when '0' then '编辑' when '1' then '发布' else '冻结' end as EditStatus,case IsBZGY when '是' then '标准' when '否' then '代用' else '' end as IsBZGY,case IsImg when '1' then '工艺' when '0' then '优选' else '' end as IsImg,Sign_MP,FProductionNO ,FProductionName,ProductUnit,ProduWorkshop,FQuantity_GB,case RoughsType when 'L' then ''  when 'Y' then ''  when 'T' then RIGHT(MaterialSizes_SJBL,5)   when 'K' then RIGHT(MaterialSizes_SJBL,5) else '' end as MaterialSizes_K1,MaterialSizes_J,FQuantity_GBZL,FYZJK,FYKJJ,FYZJK_GY,KZLJ,PartsNetWeight,MaterialQuota_GY,MaterialQuota_TL,LYL_GY,LYL_TL,case FiberDirection when 'A' then '是' else '' end as FiberDirection,case RoughsType when 'K' then '块料' when 'T' then '条料' when 'L' then '落料'  when 'Y' then '异形' else '' end as RoughsType,RoughsSize,KFGC,KZGC,CFGC,CZGC,StandardNumber_YCL,MaterialSizes_SJBL,MaterialDemand_YCL,MaterialDemand_SJBL,FLGG1,FLZL1,FLGG2,FLZL2,FLGG3,FLZL3,YLFX1,YLGG1,YLSL1,YLLY1,YLFX2,YLGG2,YLSL2,YLLY2,YLFX3,YLGG3,YLSL3,YLLY3
                                from {0}
                                )a
                where  MatListID = @MatListID
                                ",tableName= "V_XLK_22_BJ_MatCardListAcco";

            if (isArchive)
            {
                tableName = "V_XLK_21_MX_MatCardListAcco";
            }

            sql = string.Format(sql, tableName);

            result = new Dictionary<string, object>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@MatListID", MatListID)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                if (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        result.Add(reader.GetName(i), reader[i]);
                    }
                }
            }

            return result;
        }


    }
}

